﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace DBWorker
{
    public class DBWorkerAccess : DBWokerBase
    {
        #region Constructors and Fields
        public string _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}";

        public DBWorkerAccess(string path) : base(path)
        {
        }
        #endregion Constructors and Fields


        #region Public Methods
        public override string GetConnectionString()
        {
            return String.Format(_connectionString, base.Server);
        }


        public DataTable GetDataTableByCommand(string query)
        {
            OleDbCommand sqlCommQuery = new OleDbCommand(@query);
            return GetDataTableByCommand(sqlCommQuery);
        }

        public DataTable GetDataTableByCommand(OleDbCommand command)
        {
            OleDbConnection sqlConn = new OleDbConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.GetDataTableByCommand(command);
        }


        public DataTable GetDataTableSchema(string query)
        {
            OleDbCommand sqlCommQuery = new OleDbCommand(@query);
            return GetDataTableSchema(sqlCommQuery);
        }

        public DataTable GetDataTableSchema(OleDbCommand command)
        {
            OleDbConnection sqlConn = new OleDbConnection(GetConnectionString());
            command.Connection = sqlConn;

            return base.GetDataTableSchema(command);
        }



        // Заносим DataTable в Базу
        public void DataTableToDBInsertOrUpdate(string tableName, DataTable tableModifiedRows)
        {
            string qry = String.Format(@"SELECT {0}.*
                                            FROM [{0}];", tableName);

            OleDbConnection conn = new OleDbConnection(GetConnectionString());
            try
            {
                OleDbDataAdapter da = new OleDbDataAdapter(qry, conn);

                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                cb.RefreshSchema();

                // update
                DataTable tableUpdate = tableModifiedRows.GetChanges(DataRowState.Modified);
                da.UpdateCommand = cb.GetUpdateCommand();
                if (tableUpdate != null)
                    da.Update(tableUpdate);

                // insert
                DataTable tableInsert = tableModifiedRows.GetChanges(DataRowState.Added);
                da.InsertCommand = cb.GetInsertCommand();
                da.InsertCommand.CommandText = da.InsertCommand.CommandText.Replace(String.Format(" {0} ", tableName), String.Format(" [{0}] ", tableName));
                if (tableInsert != null)
                    da.Update(tableInsert);
            }
            catch (Exception e)
            {
                throw new Exception("Error: " + e);
            }
            finally
            {
                conn.Close();
            }
        }

        public object ExecuteScalar(OleDbCommand command, OleDbConnection connection)
        {
            command.Connection = connection;

            return base.ExecuteScalar(command);
        }
        #endregion Public Methods
    }
}
